/**
*┌──────────────────────────────────────────────────────────────┐
*│　描    述：SysUser仓储                                              
*│　作    者：sys                                            
*│　创建时间：2020-11-06 14:41:01  
*│　说    明：代码生成器生成   
*└──────────────────────────────────────────────────────────────┘
*/
using SXFramWork.Core;
using SXFramWork.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Dynamic;
using System.Threading.Tasks;

namespace SXFramWork.Repository
{
    public class SysUserRepository:RepositoryAbstract<SysUserEntity>,ISysUserRepository
    {
        public SysUserRepository(IDbContext dbContext):base(dbContext)
        {
          
        }

        public async Task<SysUserEntity> GetUserByNameAsync(string UserName)
        {
           return await FromSql.GetDataAsync("select * from SysUser where UserName=@UName", new { UName = UserName });
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public async Task<List<SysUserEntity>> Query(UserQueryModel model)
        {
            string sql = @"select distinct s.id,s.UserName,s.TrueName,s.Phone,s.Status,s.CreateTime,s.Sex,s.Adress,s.HeadPic,s.UpLoginDate,s.Remarks 
            from SysUser s left join SysOrganizeUser sou on s.Id=sou.SysUserId left join SysUserRole sur on sur.SysUserId=s.Id where 1=1 ";
            //模糊查询
            //Dictionary<string, object> check = new Dictionary<string, object>();
            //if (model.SysOrganizeId.NotNull())
            //{
            //    check.Add("AccountName", model.AccountName);
            //}
            dynamic obj = new ExpandoObject();
            string where = string.Empty;
            if (model.SysOrganizeId.NotNull())
            {
                where += " and sou.SysOrganizeId=@orgId";
                obj.orgId = model.SysOrganizeId;
            }
            if (model.SysRoleId.NotNull())
            {
                where += " and sur.sysroleid=@roleId";
                obj.roleId = model.SysRoleId;
            }
            //添加模糊查询条件TODO
            return await FromSql.GetPageListAsync(sql, model,null,where,obj);

        }
        public async Task<List<UserExcelModel>> GetUserInfoExcel()
        {
           return await FromSql.GetDataListAsync<UserExcelModel>(@"select UserName,sr.Name RoleName,TrueName,(case Sex when 0 then N'男' else N'女' end)Sex,
           Phone,Email, Adress from SysUser su left join SysUserRole sur on sur.SysUserId=su.Id left join SysRole sr on sr.Id=sur.SysRoleId");
        }

        public async Task<int> AddDTable(DataTable table)
        {
            var result = 0;
            var pwd = "123456";
            List<SysUserEntity> list_add = new List<SysUserEntity>();
            List<SysUserEntity> list_update = new List<SysUserEntity>();
            foreach (DataRow row in table.Rows)
            {
                var data = new SysUserEntity();
                data.UserName = row["账号"].To_String();
                data.TrueName = row["真实姓名"].To_String();
                data.Phone = row["电话"].To_String();
                data.Email = row["邮箱"].To_String();
                data.Adress = row["地址"].To_String();
                data.Sex = row["性别"].To_String().Trim()== "男" ? 0 : 1;
                var count = await FromSql.GetStrAsync("select count(1) from sysuser where UserName=@uName",new {uName=data.UserName });
                if (count.To_Int() <= 0)
                {
                    // 插入数据
                    data.Id = GuidHelper.NewSequentialGuid(SequentialGuidType.SequentialAtEnd);
                    data.Password = Md5Encrypt.Encrypt(pwd);
                    data.CreateTime = DateTime.Now;
                    data.Status = 1;
                    data.Deleted = 0;
                    list_add.Add(data);
                }
                else
                {
                    // 更新数据
                    list_update.Add(data);
                }
            }
            if (list_add.Count > 0)
            {
                result += await FromSql.AddDataListAsync("insert into sysuser(Id,UserName,Password,TrueName,Phone,Email,CreateTime,Status,Deleted,Sex,Adress) values(@Id,@UserName,@Password,@TrueName,@Phone,@Email,@CreateTime,@Status,@Deleted,@Sex,@Adress)", list_add);
            }
            if (list_update.Count > 0)
            {
                var sql = @"UPDATE sysuser SET TrueName=@TrueName,Phone=@Phone,Email=@Email,Adress=@Adress,Sex=@Sex  WHERE UserName = @UserName ;";
                result += await FromSql.ExecuteAsync(sql, list_update);
            }

            return result;
        }
    }
}